﻿using Model;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Utility.Helper;

namespace BLL
{
    public class QX_PLANYEARAPPLYBLL : BaseBLL
    {
        public QX_PLANYEARAPPLYBLL(Service service)
        {
            this.Service = service;
        }

        public QX_PLANYEARAPPLYBLL()
        {
        }

        public QX_PLANYEARAPPLY Find(decimal id)
        {
            var sql = "SELECT * FROM QX_PLANYEARAPPLY WHERE N_YEARAPP_ID = {0}";
            sql = string.Format(sql, id);
            var row = RowSingeleOfNull(sql);
            if (row == null)
            {
                return null;
            }
            var entity = new QX_PLANYEARAPPLY
            {
                N_YEARAPP_ID = Convert.ToDecimal(row["N_YEARAPP_ID"]),
                N_YEAR = Convert.ToInt32(row["N_YEAR"]),
                N_BUSINESS_ID = Convert.ToDecimal(row["N_BUSINESS_ID"]),
                C_YEARAPP_NAME = Convert.ToString(row["C_YEARAPP_NAME"]),
                N_COMPANY_ID = Convert.ToDecimal(row["N_COMPANY_ID"]),
                N_USER_ID = Convert.ToDecimal(row["N_USER_ID"]),
                D_OPERATOR_TIME = Convert.ToDateTime(row["D_OPERATOR_TIME"]),
                C_REAMRK = Convert.ToString(row["C_REAMRK"]),
                N_BUSINESS_STATE = Convert.ToDecimal(row["N_BUSINESS_STATE"])
            };
            return entity;
        }

        public QX_PLANYEARAPPLY FindByBuid(decimal buid)
        {
            var sql = "SELECT * FROM QX_PLANYEARAPPLY WHERE N_BUSINESS_ID = {0}";
            sql = string.Format(sql, buid);
            var row = RowSingeleOfNull(sql);
            if (row == null)
            {
                return null;
            }
            var entity = new QX_PLANYEARAPPLY
            {
                N_YEARAPP_ID = Convert.ToDecimal(row["N_YEARAPP_ID"]),
                N_YEAR = Convert.ToInt32(row["N_YEAR"]),
                N_BUSINESS_ID = Convert.ToDecimal(row["N_BUSINESS_ID"]),
                C_YEARAPP_NAME = Convert.ToString(row["C_YEARAPP_NAME"]),
                N_COMPANY_ID = Convert.ToDecimal(row["N_COMPANY_ID"]),
                N_USER_ID = Convert.ToDecimal(row["N_USER_ID"]),
                D_OPERATOR_TIME = Convert.ToDateTime(row["D_OPERATOR_TIME"]),
                C_REAMRK = Convert.ToString(row["C_REAMRK"]),
                N_BUSINESS_STATE = Convert.ToDecimal(row["N_BUSINESS_STATE"])
            };
            return entity;
        }

        #region 公司汇总新增代码

        public bool Insert(QX_PLANYEARAPPLY entity)
        {
            entity.N_YEARAPP_ID = GetSequence("SEQ_YEARAPP_ID");
            var sql = @"INSERT INTO QX_PLANYEARAPPLY(
	N_YEARAPP_ID,
	N_YEAR,
	N_BUSINESS_ID,
	C_YEARAPP_NAME,
	N_COMPANY_ID,
	N_USER_ID,
	D_OPERATOR_TIME,
	N_BUSINESS_STATE,
    C_REAMRK)
VALUES(
	:N_YEARAPP_ID,
	:N_YEAR,
	:N_BUSINESS_ID,
	:C_YEARAPP_NAME,
	:N_COMPANY_ID,
	:N_USER_ID,
	:D_OPERATOR_TIME,
	:N_BUSINESS_STATE,
    :C_REAMRK)";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":N_YEARAPP_ID", entity.N_YEARAPP_ID, "int");
            parms.Add(":N_YEAR", entity.N_YEAR, "int");
            parms.Add(":N_BUSINESS_ID", entity.N_BUSINESS_ID, "int");
            parms.Add(":C_YEARAPP_NAME", entity.C_YEARAPP_NAME, "string");
            parms.Add(":N_COMPANY_ID", entity.N_COMPANY_ID, "int");
            parms.Add(":N_USER_ID", entity.N_USER_ID, "int");
            parms.Add(":D_OPERATOR_TIME", entity.D_OPERATOR_TIME, "datetime");
            parms.Add(":N_BUSINESS_STATE", entity.N_BUSINESS_STATE, "int");
            parms.Add(":C_REAMRK", entity.C_REAMRK, "string");
            var count = NoQuery(sql, parms);
            return count > 0 ? true : false;
        }

        /// <summary>
        /// 通过年份和公司Id查询年资金申请主表信息
        /// </summary>
        /// <param name="year">年份</param>
        /// <param name="companyId">公司Id</param>
        /// <returns></returns>
        public QX_PLANYEARAPPLY Find(int year, int companyId)
        {
            var sql = "SELECT * FROM QX_PLANYEARAPPLY T1 WHERE T1.N_BUSINESS_STATE = 2 AND T1.N_YEAR = " + year + " AND T1.N_COMPANY_ID = " + companyId;
            return ConvertToEntity(Query(sql)).FirstOrDefault();
        }

        public List<QX_PLANYEARAPPLY> ConvertToEntity(DataTable dt)
        {
            var entitys = new List<QX_PLANYEARAPPLY>();
            foreach (var row in dt.AsEnumerable())
            {
                var entity = new QX_PLANYEARAPPLY
                {
                    N_YEARAPP_ID = Convert.ToDecimal(row["N_YEARAPP_ID"]),
                    N_YEAR = Convert.ToInt32(row["N_YEAR"]),
                    N_BUSINESS_ID = Convert.ToDecimal(row["N_BUSINESS_ID"]),
                    C_YEARAPP_NAME = Convert.ToString(row["C_YEARAPP_NAME"]),
                    N_COMPANY_ID = Convert.ToDecimal(row["N_COMPANY_ID"]),
                    N_USER_ID = Convert.ToDecimal(row["N_USER_ID"]),
                    D_OPERATOR_TIME = Convert.ToDateTime(row["D_OPERATOR_TIME"]),
                    C_REAMRK = Convert.ToString(row["C_REAMRK"]),
                    N_BUSINESS_STATE = Convert.ToDecimal(row["N_BUSINESS_STATE"])
                };
                entitys.Add(entity);
            }
            return entitys;
        }

        #endregion 公司汇总新增代码

        public DataTable GetDataBind(string buid)
        {
            var sql = @"SELECT T2.N_PROID,
       T2.N_YDETAIL_ID,
       T2.C_PRONAME,
       T2.N_TYPE,
       T2.C_REMARK,
       CASE T2.N_IF_DETAILS
         WHEN 1 THEN
          T2.C_DETAILS_CODE
         ELSE
          NULL
       END AS C_DETAILS_CODE,
       ROUND((CASE
               WHEN T2.N_AMOUNT IS NULL THEN
                0
               ELSE
                T2.N_AMOUNT
             END),
             6) AS N_AMOUNT,
       ROUND(SUM(CASE
                   WHEN T5.N_AMOUNT IS NULL THEN
                    0
                   ELSE
                    T5.N_AMOUNT
                 END),
             6) AS N_AMOUNT_WEEK,
       ROUND(SUM(CASE
                   WHEN T5.N_USEAMOUNT IS NULL THEN
                    0
                   ELSE
                    T5.N_USEAMOUNT
                 END),
             6) AS N_USEAMOUNT_WEEK,
       CASE
         WHEN ROUND(SUM(CASE
                          WHEN T5.N_AMOUNT IS NULL THEN
                           0
                          ELSE
                           T5.N_AMOUNT
                        END),
                    6) = 0 THEN
          '0%'
         WHEN ROUND(SUM(CASE
                          WHEN T5.N_USEAMOUNT IS NULL THEN
                           0
                          ELSE
                           T5.N_USEAMOUNT
                        END),
                    6) = 0 THEN
          '0%'
         ELSE
          TO_CHAR(ROUND((ROUND(SUM(CASE
                                     WHEN T5.N_USEAMOUNT IS NULL THEN
                                      0
                                     ELSE
                                      T5.N_USEAMOUNT
                                   END),
                               6) / ROUND(SUM(CASE
                                                 WHEN T5.N_AMOUNT IS NULL THEN
                                                  0
                                                 ELSE
                                                  T5.N_AMOUNT
                                               END),
                                           6) * 100),
                        2)) || '%'
       END AS N_AMOUNTPROBABILITY
  FROM QX_PLANYEARAPPLY T1
 INNER JOIN QX_PLANYEAR_DETAIL T2
    ON T1.N_YEARAPP_ID = T2.N_YEARAPP_ID
  LEFT JOIN QS_PLANWEEKAPPLY T4
    ON T1.N_YEARAPP_ID = T4.N_YEARAPP_ID
  LEFT JOIN QS_PLANWEEK_DETAIL T5
    ON T4.N_WEEKAPP_ID = T5.N_WEEKAPP_ID
   AND T2.N_PROID = T5.N_PROID
   AND T4.N_BUSINESS_STATE = 2
 WHERE T1.N_BUSINESS_ID = :BUSINESS_ID
 GROUP BY T2.N_YDETAIL_ID,
          T2.N_PROID,
          T2.C_PRONAME,
          T2.N_TYPE,
          T2.N_IF_DETAILS,
          T2.C_DETAILS_CODE,
          T2.C_REMARK,
          T2.N_AMOUNT

 ORDER BY T2.N_YDETAIL_ID
";
            var parms = new List<Tuple<string, string, string>>();
            parms.Add(":BUSINESS_ID", Convert.ToInt32(buid), "int");
            return Query(sql, parms);
            //           string sql = @"select t.n_proid,
            //      t.n_ydetail_id,
            //      t.c_proname,
            //      to_char(decode(t.n_amount, 0, null, n_amount),'FM9999999999.00') n_amount,
            //      t.n_type,
            //      t.c_remark
            // from qx_planyear_detail t, qs_business b
            //where t.n_business_id = b.n_business_id
            //  and b.n_business_id = " + buid + @"
            //order by t.n_ydetail_id";
            /*
            string sql = @"select t.n_proid,
       t.n_ydetail_id,
               t.c_proname,
               to_char(decode(t.n_amount, 0, null, t.n_amount),
                       'FM9999999999.00') n_amount,
               t.n_type,
               t.c_remark,
               round(sum(pwd.n_amount),2) n_amount_week,
               round(sum(pwd.n_useamount),2) n_useamount_week,
               decode(sum(pwd.n_amount),0,null,round(sum(pwd.n_useamount)/sum(pwd.n_amount)*100,2)||'%') n_amountProbability
          from qs_business b, qx_planyear_detail t,QS_PLANWEEKAPPLY pw,QS_PLANWEEK_DETAIL pwd
         where t.n_yearapp_id = pw.n_yearapp_id
           and pw.n_business_state = 2
           and pw.n_weekapp_id = pwd.n_weekapp_id
           and pwd.n_proid = t.n_proid
           and t.n_business_id = b.n_business_id
           and b.n_business_id = " + buid + @"
          group by t.n_proid,t.n_ydetail_id,t.c_proname,t.n_amount,t.n_type,t.c_remark order by n_ydetail_id";

            DataTable dt = base.Query(sql, "", "", "", "");

            if (dt.Rows.Count == 0)
            {
                sql = @"select t.n_proid,
                      t.n_ydetail_id,
                      t.c_proname,
                      to_char(decode(t.n_amount, 0, null, n_amount),'FM9999999999.00') n_amount,
                      t.n_type,
                      t.c_remark,
                      null n_amount_week,
                      null n_useamount_week,
                      null n_amountProbability
                 from qx_planyear_detail t, qs_business b
                where t.n_business_id = b.n_business_id
                  and b.n_business_id = " + buid + @"
                order by t.n_ydetail_id";
            }

            dt = base.Query(sql, "", "", "", "");

            return dt;
            */
        }

        /// <summary>
        ///
        /// </summary>
        /// <param name="whr"></param>
        /// <param name="logname"></param>
        /// <returns></returns>
        public DataTable BindBussBy(string whr, string logname)
        {
            string sql = @"select b.n_business_state, t.*
  from qx_planyearapply t, qs_business b
 where t.n_business_id = b.n_business_id " + whr;

            DataTable dt = base.Query(sql, "", "BindBussBy", logname, logname);

            return dt;
        }

        /// <summary>
        /// 批量修改年计划明细
        /// </summary>
        /// <param name="list"></param>
        /// <param name="logname"></param>
        /// <returns></returns>
        public int Updates(List<QX_PLANYEAR_DETAIL> list, string logname)
        {
            string sql = "";
            List<string> cList = new List<string>();
            for (int i = 0; i < list.Count; i++)
            {
                QX_PLANYEAR_DETAIL model = list[i];
                sql = @" update qx_planyear_detail
   set n_amount = " + model.N_AMOUNT + @", c_remark = '" + model.C_REMARK + @"',
D_OPERATORTIME=sysdate
 where n_ydetail_id = " + model.N_YDETAIL_ID;
                cList.Add(sql);
            }

            int rec = base.SQLSExcute(cList);//base.SQLSExcute(cList, "批量修改年计划明细", "Updates", logname, logname);
            return rec;
        }

        public void Update(JArray arry, int applyId)
        {
            try
            {
                //解析为实体并更新

                var sql = @"UPDATE QX_PLANYEAR_DETAIL
                        SET
                            N_AMOUNT = :N_MONEY,
                            C_REMARK = :C_REMARK
                        WHERE
                            N_YEARAPP_ID = :N_APPLY_ID AND
                            N_PROID = :N_PROJECT_ID";
                var entitys = new List<QX_PLANYEAR_DETAIL>();
                var sqlArry = new List<Tuple<string, List<Tuple<string, string, string>>>>();
                foreach (JObject item in arry)
                {
                    var parms = new List<Tuple<string, string, string>>();

                    parms.Add(":N_MONEY", item["Money"].ToObject<decimal>(), "decimal");
                    parms.Add(":C_REMARK", item["Remark"].ToObject<string>(), "string");
                    parms.Add(":N_APPLY_ID", applyId, "int");
                    parms.Add(":N_PROJECT_ID", item["ProjectId"].ToObject<int>(), "int");
                    sqlArry.Add(new Tuple<string, List<Tuple<string, string, string>>>(sql, parms));
                }/*
                var sqlArry = new List<Tuple<string, List<Tuple<string, string, string>>>>();
                foreach (JObject item in arry)
                {
                    var sql = "UPDATE QX_PLANYEAR_DETAIL SET N_AMOUNT = {0},C_REMARK='{1}' WHERE N_YEARAPP_ID ={2} AND N_PROID ={3}";
                    var parms = new List<Tuple<string, string, string>>();
                    //parms.Add(":N_PROJECT_ID", item["ProjectId"].ToObject<int>(), "int");
                    //parms.Add(":N_MONEY", item["Money"].ToObject<decimal>(), "decimal");
                    //parms.Add(":C_REMARK", item["Remark"].ToObject<string>(), "string");
                    //parms.Add(":N_APPLY_ID", applyId, "int");
                    sql = string.Format(sql, item["Money"].ToObject<decimal>(), item["Remark"].ToObject<string>(), applyId, item["ProjectId"].ToObject<int>());
                    sqlArry.Add(new Tuple<string, List<Tuple<string, string, string>>>(sql, parms));
                }*/
                var count = SQLSExcute(sqlArry);
            }
            catch (Exception ex)
            {
                throw new Exception("年资金计划更新失败");
            }
        }

        /// <summary>
        /// 获取申请详情信息
        /// </summary>
        /// <param name="applyId">申请Id</param>
        /// <returns></returns>
        public JArray GetData(int applyId)
        {
            //查询Detail表数据
            var sql3 = "SELECT * FROM QX_PLANYEAR_DETAIL T1 WHERE T1.N_YEARAPP_ID = " + applyId + " ORDER BY T1.N_YDETAIL_ID";
            JArray arry3 = JArray.FromObject(Query(sql3));
            return arry3;
        }
    }
}